/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package jmenu.main;

import java.awt.image.BufferedImage;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import jmenu.dbmodels.*;
import java.sql.*;
import javax.imageio.ImageIO;
import javax.swing.ImageIcon;
import javax.swing.JOptionPane;

/**
 *
 * @author Roman
 */
public class DBQueries
{
    private Connection conn;
    
    public DBQueries(Connection con)
    {
        this.conn = con;
    }
    
    public MProfile loadProfileByLogin(String login)
    {
        try
        {        
            Statement s = this.conn.createStatement();
            s.executeQuery ("SELECT * FROM profile WHERE login=\""+login+"\"");
            ResultSet rs = s.getResultSet();
            MProfile profile = null;
            
            if(rs.next())
            {   
                profile = new MProfile();
                profile.setId(rs.getInt ("id"));
                profile.setLogin(rs.getString("login"));
                profile.setPass(rs.getString("pass"));
                profile.setName(rs.getString("name"));
                profile.setSurname(rs.getString("surname"));
                profile.setSex(rs.getInt("gender"));
                profile.setHeight(rs.getInt ("height"));
                profile.setAge(rs.getDate("age"));
                profile.setWeight(Config.getKernel().queries.getMaxWeightOfUser(profile.getId()).getWeight());
            }
            rs.close ();
            s.close ();
            return profile;  
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(Config.getMainFrame(), "Chyba v dotazu na databázi.");
            return null;
        }
    }
   
    public MSectionsOfFood loadAllSectionOfFoodOrderBySectionId()
    {
        try
        {        
            Statement s = this.conn.createStatement();
            s.executeQuery ("SELECT * FROM section_of_food ORDER BY sectionid");
            ResultSet rs = s.getResultSet();
            MSectionsOfFood sections = new MSectionsOfFood();
            MSectionOfFood section = null;
            
            while(rs.next())
            {   
                section = new MSectionOfFood();
                
                section.setId(rs.getInt ("id"));
                section.setSectionid(rs.getInt ("sectionid"));
                section.setName(rs.getString("name"));
                
                sections.setRow(section);
            }
            rs.close ();
            s.close ();
            return sections;
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(Config.getMainFrame(), "Chyba v dotazu na databázi.");
            return null;
        }
    }
    
    public MSubsectionsOfFood loadAllSubsectionOfFoodBySectionIdOrderBySubsectionId(int subsectionId)
    {
        try
        {        
            Statement s = this.conn.createStatement();
            s.executeQuery ("SELECT * FROM subsection_of_food WHERE section_id=\""+subsectionId+"\" ORDER BY subsectionid");
            ResultSet rs = s.getResultSet();
            MSubsectionsOfFood subsections = new MSubsectionsOfFood();
            MSubsectionOfFood subsection = null;
            
            while(rs.next())
            {   
                subsection = new MSubsectionOfFood();
                
                subsection.setId(rs.getInt ("id"));
                subsection.setSectionId(rs.getInt("section_id"));
                subsection.setSubsectionid(rs.getInt ("subsectionid"));
                subsection.setName(rs.getString("name"));
                
                subsections.setRow(subsection);
            }
            rs.close ();
            s.close ();
            return subsections;
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(Config.getMainFrame(), "Chyba v dotazu na databázi.");
            return null;
        }
    }
     
    public MFoods loadAllFoodsBySubsectionID(int subsectionId)
    {
        try
        {        
            Statement s = this.conn.createStatement();
            s.executeQuery ("SELECT * FROM food WHERE subsection_id=\""+subsectionId+"\"");
            ResultSet rs = s.getResultSet();
            MFoods foods = new MFoods();
            MFood food = null;
            
            while(rs.next())
            {   
                food = new MFood();
                
                food.setId(rs.getInt ("id"));
                food.setSubsectionId(rs.getInt ("subsection_id"));
                food.setName(rs.getString("name"));
                food.setEnergy(rs.getDouble("energy"));
                food.setCarbohydrate(rs.getDouble("carbohydrate"));
                food.setProtein(rs.getDouble("protein"));
                food.setFat(rs.getDouble("fat"));
                food.setCholesterol(rs.getDouble("cholesterol"));
                food.setPictureId(rs.getInt ("picture_id"));
                food.setState(rs.getString("state"));
                food.setAmount(rs.getInt("amount"));
                food.setDescription(rs.getString("description"));
                
                foods.setRow(food);
            }
            rs.close ();
            s.close ();
            return foods;
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(Config.getMainFrame(), "Chyba v dotazu na databázi.");
            return null;
        }
    }

    public MFoods searchFoods(String text)
    {
        try
        {
            Statement s = this.conn.createStatement();
            s.executeQuery ("SELECT * FROM food WHERE name LIKE \"%"+text+"%\"");
            ResultSet rs = s.getResultSet();
            MFoods foods = new MFoods();
            MFood food = null;

            while(rs.next())
            {
                food = new MFood();
                food.setId(rs.getInt ("id"));
                food.setSubsectionId(rs.getInt ("subsection_id"));
                food.setName(rs.getString("name"));
                food.setEnergy(rs.getDouble("energy"));
                food.setCarbohydrate(rs.getDouble("carbohydrate"));
                food.setProtein(rs.getDouble("protein"));
                food.setFat(rs.getDouble("fat"));
                food.setCholesterol(rs.getDouble("cholesterol"));
                food.setPictureId(rs.getInt ("picture_id"));
                food.setState(rs.getString("state"));
                food.setAmount(rs.getInt("amount"));
                food.setDescription(rs.getString("description"));

                foods.setRow(food);
            }
            rs.close ();
            s.close ();
            return foods;
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(Config.getMainFrame(), "Chyba v dotazu na databázi.");
            return null;
        }
    }


    public MFoods loadAllFoodsBySectionID(int sectionId)
    {
        try
        {
            Statement s = this.conn.createStatement();
            s.executeQuery ("SELECT * FROM get_food_by_section WHERE section_id=\""+sectionId+"\"");
            ResultSet rs = s.getResultSet();
            MFoods foods = new MFoods();
            MFood food = null;

            while(rs.next())
            {
                food = new MFood();

                food.setId(rs.getInt ("id"));
                food.setSubsectionId(rs.getInt ("section_id"));
                food.setName(rs.getString("name"));
                food.setEnergy(rs.getDouble("energy"));
                food.setCarbohydrate(rs.getDouble("carbohydrate"));
                food.setProtein(rs.getDouble("protein"));
                food.setFat(rs.getDouble("fat"));
                food.setCholesterol(rs.getDouble("cholesterol"));
                food.setPictureId(rs.getInt ("picture_id"));
                food.setState(rs.getString("state"));
                food.setAmount(rs.getInt("amount"));
                food.setDescription(rs.getString("description"));
                foods.setRow(food);
            }
            rs.close ();
            s.close ();
            return foods;
        }
        catch (SQLException ex)
        {

            JOptionPane.showMessageDialog(Config.getMainFrame(), "Chyba v dotazu na databázi: "+ ex.getMessage());
            return null;
        }
    }

    public MGetImage loadGetImageById(int id)
    {
        try
        {
            conn.setAutoCommit(false);
            Statement s = this.conn.createStatement();
            s.executeQuery ("SELECT * FROM get_image WHERE id=\""+id+"\"");
            ResultSet rs = s.getResultSet();
            MGetImage image = null;

            if(rs.next())
            {
                image = new MGetImage();
                image.setId(rs.getInt ("id"));
                image.setImage(ImputstreamToImageIcon(rs.getBinaryStream("image")));
                image.setName(rs.getString("name"));
                conn.commit();
            }
            rs.close ();
            s.close ();
            return image;
        }
        catch (SQLException ex)
        {
            JOptionPane.showMessageDialog(Config.getMainFrame(), "Chyba v dotazu na databázi.");
            return null;
        }
    }

    private ImageIcon ImputstreamToImageIcon(InputStream is)
    {
        BufferedImage img = null;
        try
        {
            img = ImageIO.read(is);
        }
        catch (IOException ex)
        {
            Logger.getLogger(DBQueries.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return new ImageIcon(img); 
    }

    public MWeightsOfUser loadAllWeightOfUser(int userId)
    {
        try
        {
            Statement s = this.conn.createStatement();
            s.executeQuery ("SELECT * FROM weight_of_users WHERE user_id=\""+userId+"\" ORDER BY date ASC");
            ResultSet rs = s.getResultSet();
            MWeightsOfUser weights = new MWeightsOfUser();
            MWeightOfUser weight = null;

            while(rs.next())
            {
                weight = new MWeightOfUser();

                weight.setId(rs.getInt ("id"));
                weight.setUserId(rs.getInt ("user_id"));
                weight.setDate(rs.getTimestamp("date"));
                weight.setWeight(rs.getInt ("weight"));
                weights.setRow(weight);
            }
            rs.close ();
            s.close ();
            return weights;
        }
        catch (SQLException ex)
        {

            JOptionPane.showMessageDialog(Config.getMainFrame(), "Chyba v dotazu na databázi: "+ ex.getMessage());
            return null;
        }
    }

    public MWeightOfUser getMaxWeightOfUser(int userId)
    {
        try
        {
            Statement s = this.conn.createStatement();
            s.executeQuery ("SELECT * FROM weight_of_users WHERE user_id=\""+userId+"\" ORDER BY date DESC LIMIT 1");
            ResultSet rs = s.getResultSet();
            MWeightOfUser weight = null;

            while(rs.next())
            {
                weight = new MWeightOfUser();

                weight.setId(rs.getInt ("id"));
                weight.setUserId(rs.getInt ("user_id"));
                weight.setDate(rs.getTimestamp("date"));
                weight.setWeight(rs.getInt ("weight"));
            }
            rs.close ();
            s.close ();
            return weight;
        }
        catch (SQLException ex)
        {

            JOptionPane.showMessageDialog(Config.getMainFrame(), "Chyba v dotazu na databázi: "+ ex.getMessage());
            return null;
        }
    }


}
